package com.atomcorp.servicio;

import com.atomcorp.modelo.Catalogo;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

/**
 *
 * @author Harold
 */
public class CatalogoServicio {
    
    public ObservableList<Catalogo> listarCatalogos() {        
        ObservableList<Catalogo> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT * FROM catalogo WHERE estado = 1 ORDER BY nombre";
                Statement stm = Conexion.getConection().createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    data.add(new Catalogo(rs.getLong("ID"), rs.getString("NEMONICO"), rs.getString("NOMBRE"), 
                            rs.getString("DESCRIPCION"), rs.getBoolean("ESTADO")));
                }
                rs.close();
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    public ObservableList<Catalogo> obtenerCatalogoPorCriterio(String criterio, String valor) {
        ObservableList<Catalogo> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT * FROM catalogo WHERE " + criterio + " LIKE '%" + valor + "%' AND estado = 1";
                Statement stm = Conexion.getConection().createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    data.add(new Catalogo(rs.getLong("ID"), rs.getString("NEMONICO"), rs.getString("NOMBRE"), 
                            rs.getString("DESCRIPCION"), rs.getBoolean("ESTADO")));
                }
                rs.close();
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    public void ingresar(Catalogo catalogo) {
        try {
            if (Conexion.getConection() != null) {
                String sql = " INSERT INTO catalogo "
                        + " (NEMONICO, NOMBRE, DESCRIPCION, ESTADO) VALUES (?,?,?,?) ";
                Statement stm = Conexion.getConection().createStatement();
                PreparedStatement prep = Conexion.getConection().prepareStatement(sql);
                prep.setString(1, catalogo.getNemonico());
                prep.setString(2, catalogo.getNombre());
                prep.setString(3, catalogo.getDescripcion());
                prep.setBoolean(4, catalogo.getEstado());
                prep.addBatch();
                prep.executeBatch();
                Conexion.getConection().setAutoCommit(true);
                prep.close();
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoServicio.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    public void editar(Catalogo catalogo) {
        try {
            if (Conexion.getConection() != null) {
                String sql = " UPDATE catalogo "
                        + " SET NEMONICO = ?, NOMBRE = ?, DESCRIPCION = ?, ESTADO = ? "
                        + " WHERE ID = ?";
                Statement stm = Conexion.getConection().createStatement();
                PreparedStatement prep = Conexion.getConection().prepareStatement(sql);
                prep.setString(1, catalogo.getNemonico());
                prep.setString(2, catalogo.getNombre());
                prep.setString(3, catalogo.getDescripcion());
                prep.setBoolean(4, catalogo.getEstado());
                prep.setLong(5, catalogo.getId());
                prep.addBatch();
                prep.executeBatch();
                Conexion.getConection().setAutoCommit(true);
                prep.close();
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoServicio.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    public boolean eliminar(Long id) {
        boolean ok = false;
        try {
            if (Conexion.getConection() != null) {
                String sql = " UPDATE catalogo "
                        + " SET ESTADO = 0 WHERE ID = ? ";
                Statement stm = Conexion.getConection().createStatement();
                PreparedStatement prep = Conexion.getConection().prepareStatement(sql);
                prep.setLong(1, id);
                prep.addBatch();
                prep.executeBatch();
                Conexion.getConection().setAutoCommit(true);
                ok = true;
                prep.close();
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return ok;
    }
    
}
